Solution: Use BLOB Data Types As Needed
Let's use the BLOB data type for storing images.
If any of the issues described in the previous lesson of this chapter apply to us, we should consider storing images inside the database instead of in external files. All database brands support the BLOB data type, which we can use to store any binary data.
Advantages of storing an image in a BLOB column#
If we store an image in a BLOB column, all the issues mentioned above are resolved:
-
The image data is stored in the database. There is no extra step to load it and there’s no risk that the file’s pathname is incorrect.
-
Deleting a row deletes the image automatically.
-
Changes to an image are not visible to other clients until we commit the change.
-
Rolling back a transaction restores the previous state of the image.
-
Updating a row creates a lock so that no other client can update the same image concurrently.
-
Database backups include all the images.
-
SQL privileges control access to the image as well as the row.
Data types related to image#
The maximum size for a BLOB varies by database brand, but it’s enough to store most images. All databases support BLOB or something akin to it. MySQL, for example, provides a data type called MEDIUMBLOB that stores up to 16 megabytes, which is enough for most images. Oracle supports data types called LONG RAW and BLOB, with capacities of up to 2 and 4 gigabytes, respectively. Similar data types are available in other database brands.
Images usually exist in a file to begin with, so we need some way to load them into a BLOB ccolumn in the database. Some databases provide functions to load external files. For example, MySQL has a function called LOAD_FILE(), which we can use to read a file, typically to store the content in a BLOB column.
We can also save the contents of a BLOB column to a file. For example, MySQL has an optional clause of the SELECT statement to store the result of a query verbatim, without any formatting to denote column or row termination.
Fetching image in a web application#
We can also fetch the image data from the BLOB and output it directly. In a web application, we can output binary content such as an image, but we need to set the content type appropriately.
<?php
header('Content-type: image/jpg');
＄stmt = ＄pdo->query("SELECT screenshot_image FROM Screenshots WHERE bug_id = 1234 AND image_id = 1");
＄row = ＄stmt->fetch();
print ＄row[0];
?>
